package com.zfzs.post.modules.flow.repository;

import com.zfzs.post.modules.flow.domain.FlowActiv;
import com.zfzs.post.modules.flow.service.dto.ActivListDTO;
import com.zfzs.post.modules.flow.service.dto.InfoDetail;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
* @author wq
* @date 2019-10-21
*/
public interface FlowActivRepository extends JpaRepository<FlowActiv, Long>, JpaSpecificationExecutor {
    /**
     * 根据返现金额所属人获取返现列表
     * @param agentId
     * @return
     */
    List<FlowActiv> findByAgentId(Long agentId);

    List<FlowActiv> findByAgentIdAndActivAgentIn(Long agentId,List<Long> ids);

    @Query(nativeQuery = true, value="select * from flow_activ where agent_id=:agentId and activ_agent in :ids and create_time between :startDate and :endDate")
    List<FlowActiv> findActivByAgentId(Long agentId, List<Long> ids, String startDate, String endDate);

    @Query(nativeQuery = true, value = "select * from flow_activ where agent_id=:agentId and create_time between :startDate and :endDate")
    List<FlowActiv> findByAgentIdAndTime(Long agentId, String startDate, String endDate);

    @Query(nativeQuery = true, value = "select * from flow_activ where agent_id= :agentId and activ_agent=:agentId and create_time between :startDate and :endDate")
    List<FlowActiv> findByOwnerActiv(Long agentId, String startDate, String endDate);

    @Query(nativeQuery = true, value = "select * from flow_activ where agent_id= :agentId and activ_agent=:agentId")
    List<FlowActiv> findByOwnerActiv(Long agentId);

    @Query(nativeQuery = true,value = "SELECT DATE_FORMAT(fa.create_time,'%Y-%m-%d') as createTime,concat(ty.terminal_name,ty.model) as model,CONCAT(fa.sn,'(',ma.merchant_name,')') as sn,fa.amount as amount from flow_activ fa INNER JOIN merchant ma on fa.sn=ma.sn "+
                            "INNER JOIN post_terminal_type ty on ma.terminal_type_id =ty.id "  +
                            "where fa.agent_id=:agentId ")
    List<Map<String,Object>> findInfoList(Long agentId, Pageable pageable);

    @Query(nativeQuery = true,value = "SELECT DATE_FORMAT(fa.create_time,'%Y-%m-%d') as createTime,concat(ty.terminal_name,ty.model) as model,CONCAT(fa.sn,'(',ma.merchant_name,')') as sn,fa.amount as amount from flow_activ fa INNER JOIN merchant ma on fa.sn=ma.sn "+
            "INNER JOIN post_terminal_type ty on ma.terminal_type_id =ty.id "  +
            "where fa.agent_id=:agentId and fa.create_time between :startDate and :endDate")
    List<Map<String,Object>> findInfoList(Long agentId, String startDate, String endDate, Pageable pageable);

    List<FlowActiv> findBySn(String sn);
}